﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

/// <summary>
/// Dataaccess class for Flight
/// </summary>
namespace DBLayer
{
    public class FlightDB
    {
        //Variabler initialization
        private SqlConnection con;
        private AirportDB airportDB;
        private SqlUtility sqlUtility;

        
        public FlightDB()
        {
            airportDB = new AirportDB();
            sqlUtility = new SqlUtility();
        }
        //Insert Flight into database 
        public int InsertFlight(Flight _flight)
        {
            SqlConnection connect = sqlUtility.GetCon();
            const string query = "INSERT INTO Flight (routeId, startAirportCode, endAirportCode, price) VALUES (@routeId, @startAirportCode, @endAirportCode, @price)";
            connect.Open();
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
            cmd.Parameters.AddWithValue("@startAirportCode", _flight.Departure.AirportCode);
            cmd.Parameters.AddWithValue("@endAirportCode", _flight.Arrival.AirportCode);
            cmd.Parameters.AddWithValue("@price", _flight.Price);

            return sqlUtility.ExecuteData(cmd, connect);
        }

        //returns a list<Flight> by string (airportcode)
        public List<Flight> FindFlightByAirport(string _airportCode)
        {
            List<Flight> flightList = new List<Flight>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Flight WHERE startAirportCode = @airportCode";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@startAirportCode", _airportCode);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Flight temFlight = BuildFlight(rdr);
                            flightList.Add(temFlight);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (Flight flight in flightList)
            {
                flight.Arrival = airportDB.FindAirportByCode(flight.Arrival.AirportCode);
                flight.Departure = airportDB.FindAirportByCode(flight.Departure.AirportCode);
            }
            return flightList;
        }
        //Returns the flight between two airports
        public Flight FindFlightByAirports(Airport _startAirport, Airport _endAirport)
        {
            bool exists = false;
            Flight tempFlight = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Flight WHERE startAirportCode=@startAirportCode and endAirportCode=@endAirportCode";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@startAirportCode", _startAirport.AirportCode);
                    cmd.Parameters.AddWithValue("@endAirportCode", _endAirport.AirportCode);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                       if (rdr.Read())
                        {
                            tempFlight = BuildFlight(rdr);
                            exists = true;
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            if (exists)
            {
                tempFlight.Arrival = airportDB.FindAirportByCode(tempFlight.Arrival.AirportCode);
                tempFlight.Departure = airportDB.FindAirportByCode(tempFlight.Departure.AirportCode);
            }
            return tempFlight;
        }
        //find flight by string (route Id)
        public Flight FindFlightByID(int _routeId)
        {
            bool exists = false;
            Flight temFlight = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Flight WHERE routeId = @routeId";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@routeId", _routeId);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if (rdr.Read())
                        {
                            temFlight = BuildFlight(rdr);
                            exists = true;
                        }

                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            if (exists)
            {
                temFlight.Arrival = airportDB.FindAirportByCode(temFlight.Arrival.AirportCode);
                temFlight.Departure = airportDB.FindAirportByCode(temFlight.Departure.AirportCode);

            }
            return temFlight;
        }
        //Update Flight in database by obejct 
        public int UpdateFlight(Flight _flight)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            const string query = "UPDATE Flight SET routeId = @routeId, startAirportCode = @startAirportCode, endAirportCode = @endAirportCode, price = @price WHERE routeId = @routeId";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@startAirportCode", _flight.Departure.AirportCode);
            cmd.Parameters.AddWithValue("@endAirportCode", _flight.Arrival.AirportCode);
            cmd.Parameters.AddWithValue("@price", _flight.Price);
            cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
            return sqlUtility.ExecuteData(cmd, connect);
        }
        //build object Flight from data from database
        private Flight BuildFlight(SqlDataReader _reader)
        {
            Flight temFlight = new Flight();
            temFlight.RouteId = (int) _reader["routeId"];
            decimal temPrice = (decimal) _reader["price"];
            temFlight.Price = System.Convert.ToDouble(temPrice);
            temFlight.Arrival = new Airport();
            temFlight.Arrival.AirportCode = (string) _reader["endAirportCode"];
            temFlight.Departure = new Airport();
            temFlight.Departure.AirportCode = (string)_reader["startAirportCode"];

            return temFlight;
        }
        //return a List<Flight> with all the Flight in the database
        public List<Flight> SelectAll()
        {
            List<Flight> flights = new List<Flight>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from Flight";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            Flight temFlight = new Flight();
                            temFlight = BuildFlight(rdr);
                            flights.Add(temFlight);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (Flight flight in flights)
            {
                if (flight.Arrival.AirportCode != null && flight.Departure.AirportCode != null)
                {
                    flight.Arrival = airportDB.FindAirportByCode(flight.Arrival.AirportCode);
                    flight.Departure = airportDB.FindAirportByCode(flight.Departure.AirportCode);
                }
            }
            return flights;
        }
        //Remove Flight from database by a object of flight
        public int RemoveFlight(Flight _flight)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from Flight WHERE routeId = @routeId";
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@routeId", _flight.RouteId);
            return sqlUtility.ExecuteData(cmd,connect);

        }
        
    }
}
